package cn.pinming.chyn.tools.utils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.Base64Utils;
import org.springframework.util.StringUtils;

import cn.pinming.chyn.tools.exception.ChynException;
import cn.pinming.chyn.tools.exception.ChynResultCode;

/**
 * Create by WangSJ on 2019/03/21
 * Excel下载工具类
 */
class ExcelUtil {

    //2003+ 版本的excel
    private final static String excel2003L = ".xls";
    //2007+ 版本的excel
    private final static String excel2007U = ".xlsx";

    /**
     * 创建excel文档，  版本为 xlsx
     *
     * @param list        数据
     * @param keys        list中map的key数组集合，用于从map中取值
     * @param columnNames excel的列名，用于生成列名
     * @param title        总标题名称
     */
    public static Workbook createWorkBook(List list, String[] keys, String[] columnNames, String title) {
        // 创建excel工作簿
        Workbook wb = new XSSFWorkbook();
        // 创建第一个sheet（页）
        Sheet sheet = wb.createSheet();
        //设置单元格格式为文本格式
        CellStyle columnStyle = wb.createCellStyle();
        DataFormat dataFormat = wb.createDataFormat();
        columnStyle.setDataFormat(dataFormat.getFormat("@"));

        // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
        for (int i = 0; i < keys.length; i++) {
            sheet.setColumnWidth(i, (int) (35.7 * 150));
            sheet.setDefaultColumnStyle(i, columnStyle);
        }
        int rowIndex = 0;

        // 创建第一行
        Row row = sheet.createRow(rowIndex);

        // 创建两种单元格格式
        CellStyle headcss = wb.createCellStyle();
        CellStyle cs = wb.createCellStyle();
        CellStyle cs2 = wb.createCellStyle();

        // 创建两种字体
        Font f = wb.createFont();
        Font f2 = wb.createFont();

        // 创建第一种字体样式（用于列名）
        f.setFontHeightInPoints((short) 10);
        f.setColor(IndexedColors.BLACK.getIndex());
        f.setBold(true);


        // 创建第二种字体样式（用于值）
        f2.setFontHeightInPoints((short) 10);
        f2.setColor(IndexedColors.BLACK.getIndex());

//        Font f3=wb.createFont();
//        f3.setFontHeightInPoints((short) 10);
//        f3.setColor(IndexedColors.RED.getIndex());

        // 用于大标题
        headcss.setFont(f);
        headcss.setDataFormat(dataFormat.getFormat("@"));
        headcss.setBorderLeft(BorderStyle.THIN);                //细边线
        headcss.setBorderRight(BorderStyle.THIN);
        headcss.setBorderTop(BorderStyle.THIN);
        headcss.setBorderBottom(BorderStyle.THIN);
        headcss.setAlignment(HorizontalAlignment.CENTER);       //水平居中对齐
        headcss.setVerticalAlignment(VerticalAlignment.CENTER); //垂直居中对齐

        // 设置第一种单元格的样式（用于列名）
        cs.setFont(f);
        cs.setDataFormat(dataFormat.getFormat("@"));
        cs.setBorderLeft(BorderStyle.THIN);
        cs.setBorderRight(BorderStyle.THIN);
        cs.setBorderTop(BorderStyle.THIN);
        cs.setBorderBottom(BorderStyle.THIN);
        cs.setAlignment(HorizontalAlignment.CENTER);
        cs.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置第二种单元格的样式（用于值）
        cs2.setFont(f2);
        cs2.setDataFormat(dataFormat.getFormat("@"));
        cs2.setBorderLeft(BorderStyle.THIN);
        cs2.setBorderRight(BorderStyle.THIN);
        cs2.setBorderTop(BorderStyle.THIN);
        cs2.setBorderBottom(BorderStyle.THIN);
        cs2.setAlignment(HorizontalAlignment.CENTER);
        cs2.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置标题
        if (StringUtils.hasText(title)) {
            row.setHeight((short) 600);
            CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, columnNames.length - 1);
            sheet.addMergedRegion(cellRangeAddress);
            Cell cells = row.createCell(0);
            cells.setCellStyle(headcss);
            cells.setCellValue(title);
            rowIndex++;
            row = sheet.createRow(rowIndex);
        }

        //设置列名
        for (int i = 0; i < columnNames.length; i++) {
            row.setHeight((short) 400);
            Cell cell = row.createCell(i);
            cell.setCellValue(columnNames[i]);
            cell.setCellStyle(cs);
        }
        rowIndex++;
        //设置每行每列的值
        for (Object o : list) {
            if (o == null)
                continue;
            Map data;
            if (o instanceof Map)
                data = (Map) o;
            else
                data = getValue(o);

            // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
            Row row1 = sheet.createRow(rowIndex);
            // 在row行上创建一个方格
            for (short j = 0; j < keys.length; j++) {
                Cell cell = row1.createCell(j);
                Object value = data.get(keys[j]);
                cell.setCellValue(value == null ? "" : value.toString());
                cell.setCellStyle(cs2);
            }
            rowIndex++;
        }
        return wb;
    }

    /**
     * 创建excel文件  版本为 .xlsx
     */
    public static void createFile(List<Map<String, Object>> list, String[] keys, String[] columnNames, String headName, File file) throws IOException {
        if (file == null)
            throw new ChynException(ChynResultCode.ERR, "文件不存在");

        try (FileOutputStream fos = new FileOutputStream(file)) {
            ExcelUtil.createWorkBook(list, keys, columnNames, headName).write(fos);
        } catch (IOException ioe) {
        	System.err.println("Excel输出失败！");
            throw ioe;
        }
    }

    /**
     * 获得object的属性（字段名）
     */
    public static Map<String, Object> getValue(Object thisObj) {
        Map<String, Object> map = new HashMap();
        try {
            Class c = Class.forName(thisObj.getClass().getName());
            Method[] m = c.getMethods();
            for (Method method : m) {
                String methodName = method.getName();
                if (methodName.startsWith("get")) {
                    try {
                        Object value = method.invoke(thisObj);
                        if (value != null) {
                            String key = methodName.substring(3);
                            key = key.substring(0, 1).toLowerCase() + key.substring(1);
                            map.put(key, value);
                        }
                    } catch (Exception e) {
                        System.out.println("error:" + method);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return map;
    }

    /**
     * Bean --> Map 1: 利用Introspector和PropertyDescriptor 将Bean --> Map
     */
    public static Map<String, Object> transBean2Map(Object obj) {
        if (obj == null) {
            return null;
        }
        Map<String, Object> map = new HashMap();
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor property : propertyDescriptors) {
                String key = property.getName();
                // 过滤class属性
                if (!key.equals("class")) {
                    // 得到property对应的getter方法
                    Method getter = property.getReadMethod();
                    Object value = getter.invoke(obj);
                    map.put(key, value);
                }

            }
        } catch (Exception e) {
            System.err.println("transBean2Map Error ");
        }
        return map;
    }

    /**
     * 导出  导出的版本为 .xlsx
     *
     * @param list        数据
     * @param columnNames 表头
     * @param keys        list中，数据的key，按照columnNames的顺序排序
     * @param head        生成Excel时，首行标题，与flag配合使用
     * @param fileName    生成Excel的文件名
     * @param request     HttpServletRequest对象，用于获取用户浏览器类型
     * @param response    HttpServletResponse对象，用于写入Excel输出流
     * @param flag        生成的excel 首行是否包含表头说明
     */
    public static void download(List list, String keys[], String columnNames[], String head, String fileName, HttpServletRequest request, HttpServletResponse response, boolean flag) {
        // 生成Excel
        Workbook workBook = ExcelUtil.createWorkBook(list, keys, columnNames, flag ? head : "");

        // 设置response参数，可以打开下载页面
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        try (OutputStream os = response.getOutputStream()) {
            // 根据浏览器，设置文件名
            String userAgent = request.getHeader("user-agent");
            if (userAgent.contains("Firefox")) {
                fileName = "=?UTF-8?B?" + Base64Utils.encodeToString(fileName.getBytes(StandardCharsets.UTF_8)) + "?=";
            } else {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");

            // Excel写到浏览器输出流
            workBook.write(os);
        } catch (IOException e) {
            throw new ChynException(ChynResultCode.ERR, "Excel下载失败！");
        }
    }

    public static void download(List list, LinkedHashMap<String, String> columnMap, String head, String fileName, HttpServletRequest request, HttpServletResponse response, boolean flag) {
        if (columnMap == null)
            throw new ChynException(ChynResultCode.NO_PARAM, "列定义集合不能为空");
        download(list,
                columnMap.values().toArray(new String[]{}),
                columnMap.keySet().toArray(new String[]{}),
                head,
                fileName,
                request,
                response,
                flag);
    }

    /**
     * Excel导入
     *
     * @param in          Excel文件流
     * @param fileName    Excel文件名称，因为要根据文件名称的后缀.xls .xlsx来决定采用哪种解析方式
     * @param startColumn 从哪一列开始解析数据，从0开始，如果不传递此参数，将自动从每一行的有效数据列开始解析
     * @param endColumn   每一行的数据，到哪一列解析结束（不包含此列），不足指定列的将补充空字符串，如果不传递此参数，每一行的数据将自动在有效数据列结束时停止解析
     */
    public static List<List<String>> getListByExcel(InputStream in, String fileName, Integer startColumn, Integer endColumn) throws Exception {
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        Row row;
        Cell cell;
        List<List<String>> list = new ArrayList();
        //遍历Excel中所有的sheet
        for (int i = 0; i < work.getNumberOfSheets(); i++) {
            Sheet sheet = work.getSheetAt(i);
            if (sheet == null) {
                continue;
            }
            //遍历当前sheet中的所有行
            //包含头部,用于校验
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                //读取一行
                row = sheet.getRow(j);
                //去掉空行
                if (row == null) {
                    continue;
                }
                // 封装每一行中的所有列，如果传递的参数不为空，将从指定的列起始值处开始取固定列的内容
                // 这种方式得到的数据能够保证每一行的长度都是固定的
                List<String> li = new ArrayList();
                int startNumber = startColumn == null ? row.getFirstCellNum() : startColumn;
                int endNumber = endColumn == null ? row.getLastCellNum() : endColumn;
                for (int y = startNumber; y < endNumber; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);
            }
        }
        return list;
    }

    /**
     *  获取第一个sheet页数据
     * @param in
     * @param fileName
     * @param startColumn
     * @param endColumn
     * @return
     * @throws Exception
     */
    public static List<List<String>> getListBySheetExcel(InputStream in, String fileName, Integer startColumn, Integer endColumn) throws Exception {
        //创建Excel工作薄
        Workbook work = getWorkbook(in, fileName);
        Row row;
        Cell cell;
        List<List<String>> list = new ArrayList();
        //遍历Excel中所有的sheet

            Sheet sheet = work.getSheetAt(0);
            if (sheet == null) {
                return list;
            }
            //遍历当前sheet中的所有行
            //包含头部,用于校验
            for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {
                //读取一行
                row = sheet.getRow(j);
                //去掉空行
                if (row == null) {
                    continue;
                }
                // 封装每一行中的所有列，如果传递的参数不为空，将从指定的列起始值处开始取固定列的内容
                // 这种方式得到的数据能够保证每一行的长度都是固定的
                List<String> li = new ArrayList();
                int startNumber = startColumn == null ? row.getFirstCellNum() : startColumn;
                int endNumber = endColumn == null ? row.getLastCellNum() : endColumn;
                for (int y = startNumber; y < endNumber; y++) {
                    cell = row.getCell(y);
                    li.add(getCellValue(cell));
                }
                list.add(li);

        }
        return list;
    }

    /**
     * Excel导入重载
     */
    public static List<List<String>> getListByExcel(InputStream in, String fileName) throws Exception {
        return getListByExcel(in, fileName, null, null);
    }

    /**
     * 根据文件后缀，自适应上传文件的版本
     */
    private static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook wb;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            //2003- 版本的excel
            //wb = new HSSFWorkbook(inStr);
            wb = new XSSFWorkbook(inStr);
        } else if (excel2007U.equals(fileType)) {
            //2007+ 版本的excel
            wb = new XSSFWorkbook(inStr);
        } else {
            throw new ChynException(ChynResultCode.ERR, "Excel版本未知");
        }
        return wb;
    }

    /**
     * 对表格中数值进行格式化
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        String value;
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getRichStringCellValue().getString().trim();
                break;
            case NUMERIC:
                //格式化日期
                if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                    value = String.valueOf(cell.getNumericCellValue());
                } else if ("yyyy\"年\"m\"月\";@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/mm;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy/m;@".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");  //日期格式化
                    value = sdf2.format(cell.getDateCellValue());
                } else if ("yyyy/mm/dd;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy\\/mm\\/dd;@".equals(cell.getCellStyle().getDataFormatString())
                        || "yyyy\"年\"mm\"月\"dd\"日\";@".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m-d-yy".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");  //日期格式化
                    value = sdf.format(cell.getDateCellValue());
                } else if ("m/d".equals(cell.getCellStyle().getDataFormatString())) {
                    SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM");  //日期格式化
                    value = sdf2.format(cell.getDateCellValue());
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = cell.getBooleanCellValue() + "";
                break;
            default:
                value = "";
                break;
        }
        return value;
    }



    /**
     * 设置 POI Sheet 单元格提示
     *
     * @param sheet 表单
     * @param promptTitle 提示标题
     * @param promptContent 提示内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     */
    public static void setPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        dataValidation.createPromptBox(promptTitle, promptContent);
        dataValidation.setShowPromptBox(true);
        sheet.addValidationData(dataValidation);
    }

    /**
     * 设置某些列的值只能输入预制的数据,显示下拉框.
     *
     * @param sheet 要设置的sheet.
     * @param textlist 下拉框显示的内容
     * @param firstRow 开始行
     * @param endRow 结束行
     * @param firstCol 开始列
     * @param endCol 结束列
     * @return 设置好的sheet.
     */
    public static void setValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 加载下拉列表内容
        DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        DataValidation dataValidation = helper.createValidation(constraint, regions);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(dataValidation);
    }

    /**
     * 下载static文件夹下的文件(文件要在static文件夹下)
     * @param fileName
     * @param response
     */
    public static void downLoadFileInStatic(HttpServletRequest request, HttpServletResponse response, String fileName) {
        if(org.apache.commons.lang3.StringUtils.isBlank(fileName)) {
            throw new ChynException(ChynResultCode.NO_PARAM, "文件名为空");
        }

        response.setCharacterEncoding(request.getCharacterEncoding());
        response.setContentType("application/octet-stream");
        InputStream inputStream = null;
        try {
            fileName = new String(fileName.getBytes(), StandardCharsets.UTF_8);
            ClassPathResource classPathResource = new ClassPathResource("static"+File.separator+fileName);
            inputStream = classPathResource.getInputStream();
            try {
                IOUtils.copy(inputStream,response.getOutputStream());
                response.flushBuffer();
            }finally {
                IOUtils.closeQuietly(inputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
